package org.demo3;

import com.alibaba.dashscope.exception.InputRequiredException;
import com.alibaba.dashscope.exception.NoApiKeyException;
import com.github.javaparser.JavaParser;
import com.github.javaparser.ParseResult;
import com.github.javaparser.ast.CompilationUnit;
import com.github.javaparser.ast.body.ClassOrInterfaceDeclaration;
import com.github.javaparser.ast.comments.Comment;
import com.github.javaparser.ast.expr.StringLiteralExpr;
import com.github.javaparser.ast.visitor.VoidVisitorAdapter;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import java.util.concurrent.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import org.demo3.bean.*;
import picocli.CommandLine;
import picocli.CommandLine.Option;

public class SqlKeywordFinder  {

    // 定义常见的 SQL 关键字
    private static final List<String> DEFAULT_SQL_KEYWORDS = Arrays.asList(
            "SELECT", "INSERT", "UPDATE", "DELETE", "FROM", "WHERE", "JOIN", "ON", "AND", "OR", "GROUP BY", "ORDER BY",
            "CREATE", "ALTER", "DROP", "TRUNCATE", "TABLE", "INDEX", "VIEW", "PROCEDURE", "FUNCTION", "CALL"
    );

    // 正则表达式用于匹配 SQL 关键字
    private static Pattern sqlKeywordPattern;

    // 输出文件路径
    private static String outputFilePath = "sql_keywords_report.txt";

    // 是否忽略注释中的 SQL 关键字
    private static boolean ignoreComments = false;

    // 存储找到的 SQL 关键字及其位置信息
    private static final List<String> results = Collections.synchronizedList(new ArrayList<>());

    // 存储结果
    private List<JavaSqlContent> resultsBean = new ArrayList<>();

    private String directoryPath = "F:\\java-project-test\\SEM_EPM_P";

//    @Option(names = {"-k", "--keywords"}, description = "Comma-separated list of SQL keywords.")
//    private String customKeywords;

//    @Option(names = {"-o", "--output"}, description = "Output file path for the report.")
    private String outputPath = "F:\\java-project-test\\sql_keywords_report.txt";

//    @Option(names = {"-i", "--ignore-comments"}, description = "Ignore SQL keywords in comments.")
    private boolean ignoreCommentsOption = true;

    public static void main(String[] args) {
        // 使用 picocli 解析命令行参数
//        int exitCode = new CommandLine(new SqlKeywordFinder()).execute(args);
//        System.exit(exitCode);
        new SqlKeywordFinder().run();
    }

    public void run() {
        // 初始化 SQL 关键字
        initializeSqlKeywords();

        // 设置输出文件路径
        if (outputPath != null) {
            outputFilePath = outputPath;
        }

        // 设置是否忽略注释
        ignoreComments = ignoreCommentsOption;

        File directory = new File(directoryPath);

        if (!directory.isDirectory()) {
            System.out.println("The provided path is not a directory.");
            return;
        }

        try {
            // 获取目录下的所有 .java 文件
            List<File> javaFiles = Files.walk(Paths.get(directoryPath))
                    .filter(Files::isRegularFile)
                    .filter(path -> path.toString().endsWith(".java"))
                    .map(Path::toFile)
                    .collect(Collectors.toList());

            // 使用多线程解析文件
            ExecutorService executor = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
            List<Future<?>> futures = new ArrayList<>();

            for (File file : javaFiles) {
                futures.add(executor.submit(() -> findSqlKeywordsInFile(file)));
            }

            // 等待所有任务完成
            for (Future<?> future : futures) {
                future.get();
            }

            // 关闭线程池
            executor.shutdown();

            // 将结果输出到文件
            writeResultsToFile();

            // 将结果输出到csv文件
            writeResultsToCsvFile();

        } catch (IOException | InterruptedException | ExecutionException e) {
            System.err.println("Error processing files: " + e.getMessage());
        }
    }

    private void initializeSqlKeywords() {
        List<String> keywords = new ArrayList<>(DEFAULT_SQL_KEYWORDS);
//        if (customKeywords != null && !customKeywords.isEmpty()) {
//            keywords.addAll(Arrays.asList(customKeywords.split(",")));
//        }
        sqlKeywordPattern = Pattern.compile("\\b(" + String.join("|", keywords) + ")\\b", Pattern.CASE_INSENSITIVE);
    }

    private void findSqlKeywordsInFile(File file) {
        try {
            FileInputStream in = new FileInputStream(file);
            // 解析 Java 文件
            JavaParser javaParser = new JavaParser();
            ParseResult<CompilationUnit> parseResult = javaParser.parse(in);
            if (parseResult.isSuccessful()) {
                CompilationUnit cu = parseResult.getResult().get();

                // 创建一个 Visitor 来查找 SQL 关键字
                cu.accept(new VoidVisitorAdapter<Void>() {
                    @Override
                    public void visit(ClassOrInterfaceDeclaration n, Void arg) {
                        super.visit(n, arg);
                        // 记录当前类名
                        String className = n.getNameAsString();
                        // 查找类中的所有字符串字面量
                        checkForSqlKeywords(className, n.findAll(StringLiteralExpr.class), file, false);
                        // 查找类中的所有注释
                        if (!ignoreComments) {
                            checkForSqlKeywords(className, n.getAllContainedComments(), file, true);
                        }
                    }
                }, null);
            }
        } catch (IOException e) {
            System.err.println("Error parsing file " + file.getAbsolutePath() + ": " + e.getMessage());
        }
    }

    private void processSqlKeywordsInContent(String className, String content, List<?> nodes, File file, boolean isComment)  {
        Matcher matcher = sqlKeywordPattern.matcher(content);
        while (matcher.find()) {
            String keyword = matcher.group();
            int start = matcher.start();
            int end = matcher.end();

            // 遍历所有节点，找到包含该关键字的节点
            for (Object node : nodes) {
                if (node instanceof StringLiteralExpr) {
                    StringLiteralExpr stringLiteral = (StringLiteralExpr) node;
//                    int nodeStart = stringLiteral.getBegin().get().line;
//                    int nodeEnd = stringLiteral.getEnd().get().line;
//                    int lineNumber = getLineNumber(stringLiteral);
                    int lineNumber = -1;
                    // 检查关键字是否在当前字符串字面量中
                    String literalValue = stringLiteral.getValue();
                    int literalStart = content.indexOf(literalValue);
                    int literalEnd = literalStart + literalValue.length();

                    if (start >= literalStart && end <= literalEnd) {
                        //调用ai接口进行优化
                        CallAiService aiService = new CallAiService();
                        String prex = "sql改为适配postgresql的语法,只需要返回sql,不要返回其他的中文";
                        String afterStr = aiService.callWithMessage(prex +  ": " + literalValue);
                        // 记录结果
                        String result =  String.format("File: %s, Class: %s, Line: %s, lineNumber: %s ,Keyword: %s, In Comment: %b, 优化后的sql片段: %s",
                                file.getAbsolutePath(), className, literalValue, lineNumber, keyword, isComment, afterStr);
                        System.out.println(result);
                        results.add(result);
                        //组织数据到bean中
                        JavaSqlContent javaSqlContent = new JavaSqlContent(file.getAbsolutePath(), className, literalValue, keyword, afterStr);
                        resultsBean.add(javaSqlContent);
                        break;
                    }
                } else if (node instanceof Comment) {
                    Comment comment = (Comment) node;
                    int nodeStart = comment.getRange().get().begin.line;
                    int nodeEnd = comment.getRange().get().end.line;

                    // 检查关键字是否在当前注释中
                    String commentContent = comment.getContent();
                    int commentStart = content.indexOf(commentContent);
                    int commentEnd = commentStart + commentContent.length();

                    if (start >= commentStart && end <= commentEnd) {
                        // 记录结果
                        results.add(String.format("File: %s, Class: %s, Line: %d-%d, Keyword: %s, In Comment: %b",
                                file.getAbsolutePath(), className, nodeStart, nodeEnd, keyword, isComment));
                        break;
                    }
                }
            }
        }
    }

    private void checkForSqlKeywords(String className, List<?> nodes, File file, boolean isComment) {
        StringBuilder combinedString = new StringBuilder();
        List<StringLiteralExpr> stringLiterals = new ArrayList<>();
        List<Comment> comments = new ArrayList<>();

        for (Object node : nodes) {
            if (node instanceof StringLiteralExpr) {
                StringLiteralExpr stringLiteral = (StringLiteralExpr) node;
                combinedString.append(stringLiteral.getValue());
                stringLiterals.add(stringLiteral);
            } else if (node instanceof Comment) {
                Comment comment = (Comment) node;
                comments.add(comment);
            }
        }

        // 合并相邻的字符串字面量
        if (!stringLiterals.isEmpty()) {
            // 创建一个新的列表来存储合并后的字符串字面量
            List<StringLiteralExpr> mergedStringLiterals = new ArrayList<>();
            StringBuilder currentMerge = new StringBuilder();
            int currentLineStart = -1;
            int currentLineEnd = -1;

            for (StringLiteralExpr stringLiteral : stringLiterals) {
                if (currentMerge.length() == 0) {
                    currentMerge.append(stringLiteral.getValue());
                    currentLineStart = stringLiteral.getBegin().get().line;
                    currentLineEnd = stringLiteral.getEnd().get().line;
                } else {
                    // 检查是否是相邻的字符串字面量
                    if (stringLiteral.getBegin().get().line == currentLineEnd + 1) {
                        currentMerge.append(stringLiteral.getValue());
                        currentLineEnd = stringLiteral.getEnd().get().line;
                    } else {
                        // 保存当前合并的字符串字面量
                        mergedStringLiterals.add(new StringLiteralExpr(currentMerge.toString()));
                        currentMerge = new StringBuilder(stringLiteral.getValue());
                        currentLineStart = stringLiteral.getBegin().get().line;
                        currentLineEnd = stringLiteral.getEnd().get().line;
                    }
                }
            }

            // 保存最后一个合并的字符串字面量
            if (currentMerge.length() > 0) {
                mergedStringLiterals.add(new StringLiteralExpr(currentMerge.toString()));
            }

            // 处理合并后的字符串字面量中的 SQL 关键字
            for (StringLiteralExpr mergedLiteral : mergedStringLiterals) {
                processSqlKeywordsInContent(className, mergedLiteral.getValue(), Collections.singletonList(mergedLiteral), file, false);
            }
        }

        // 处理注释中的 SQL 关键字
        if (!comments.isEmpty() && !ignoreComments) {
            for (Comment comment : comments) {
                processSqlKeywordsInContent(className, comment.getContent(), Collections.singletonList(comment), file, true);
            }
        }
    }

    private int getLineNumber(StringLiteralExpr stringLiteral) {
        // 读取文件内容并找到字符串字面量所在的行号
        return stringLiteral.getBegin().orElseThrow(() -> new NoSuchElementException("No line information")).line;
    }

    private void writeResultsToFile() {
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(outputFilePath))) {
            for (String result : results) {
                writer.write(result);
                writer.newLine();
            }
            System.out.println("SQL keywords report generated at: " + outputFilePath);
        } catch (IOException e) {
            System.err.println("Error writing to output file: " + e.getMessage());
        }
    }

    /**
     * 将分析结果写入到 CSV 文件中
     * 此方法主要用于将程序分析的 SQL 关键字使用情况输出到 CSV 文件，以便进一步分析或报告
     * 它首先创建一个 CSV 文件的实用程序实例，然后构造要写入的数据集，最后调用实用工具类的方法将数据写入文件
     */
    private void writeResultsToCsvFile() {
        // 创建 CSV 文件写入实用工具实例
        CsvOutputUtil csvUtil = new CsvOutputUtil();

        // 初始化数据列表，用于存储所有要写入 CSV 文件的数据
        List<List<String>> data = new ArrayList<>();

        // 添加表头，定义 CSV 文件的列名
        List<String> header =  Arrays.asList("File Path", "Class Name", "SQL Keyword", "Line Number", "In Comment");
        data.add(0, header);

        // 遍历结果列表，将每个 JavaSqlContent 实例的相关信息添加到数据列表中
        for(JavaSqlContent javaSqlContent: resultsBean){
            List<String> content = new ArrayList<>();
            content.add(javaSqlContent.getFile());
            content.add(javaSqlContent.getClassName());
            content.add(javaSqlContent.getKeyword());
            content.add(javaSqlContent.getLine());
            content.add(javaSqlContent.getOptLine());
            data.add(content);
        }
        // 调用 CSV 文件写入实用工具的方法，将数据写入指定路径的 CSV 文件
        csvUtil.writeCsvFile(data, "F:\\java-project-test\\output.csv");
    }
}

	